<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>pg_locks</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.1.2 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="System Catalogs"
HREF="catalogs.html"><LINK
REL="PREVIOUS"
TITLE="pg_indexes"
HREF="view-pg-indexes.html"><LINK
REL="NEXT"
TITLE="pg_prepared_statements"
HREF="view-pg-prepared-statements.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2011-12-01T22:07:59"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.1.2 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="pg_indexes"
HREF="view-pg-indexes.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="catalogs.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 45. System Catalogs</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="pg_prepared_statements"
HREF="view-pg-prepared-statements.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="VIEW-PG-LOCKS"
>45.56. <TT
CLASS="STRUCTNAME"
>pg_locks</TT
></A
></H1
><P
>   The view <TT
CLASS="STRUCTNAME"
>pg_locks</TT
> provides access to
   information about the locks held by open transactions within the
   database server.  See <A
HREF="mvcc.html"
>Chapter 13</A
> for more discussion
   of locking.
  </P
><P
>   <TT
CLASS="STRUCTNAME"
>pg_locks</TT
> contains one row per active lockable
   object, requested lock mode, and relevant transaction.  Thus, the same
   lockable object might
   appear many times, if multiple transactions are holding or waiting
   for locks on it.  However, an object that currently has no locks on it
   will not appear at all.
  </P
><P
>   There are several distinct types of lockable objects:
   whole relations (e.g., tables), individual pages of relations,
   individual tuples of relations,
   transaction IDs (both virtual and permanent IDs),
   and general database objects (identified by class OID and object OID,
   in the same way as in <TT
CLASS="STRUCTNAME"
>pg_description</TT
> or
   <TT
CLASS="STRUCTNAME"
>pg_depend</TT
>).  Also, the right to extend a
   relation is represented as a separate lockable object.
   Also, <SPAN
CLASS="QUOTE"
>"advisory"</SPAN
> locks can be taken on numbers that have
   user-defined meanings.
  </P
><DIV
CLASS="TABLE"
><A
NAME="AEN89981"
></A
><P
><B
>Table 45-57. <TT
CLASS="STRUCTNAME"
>pg_locks</TT
> Columns</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><COL><THEAD
><TR
><TH
>Name</TH
><TH
>Type</TH
><TH
>References</TH
><TH
>Description</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="STRUCTFIELD"
>locktype</TT
></TD
><TD
><TT
CLASS="TYPE"
>text</TT
></TD
><TD
>&nbsp;</TD
><TD
>       Type of the lockable object:
       <TT
CLASS="LITERAL"
>relation</TT
>,
       <TT
CLASS="LITERAL"
>extend</TT
>,
       <TT
CLASS="LITERAL"
>page</TT
>,
       <TT
CLASS="LITERAL"
>tuple</TT
>,
       <TT
CLASS="LITERAL"
>transactionid</TT
>,
       <TT
CLASS="LITERAL"
>virtualxid</TT
>,
       <TT
CLASS="LITERAL"
>object</TT
>,
       <TT
CLASS="LITERAL"
>userlock</TT
>, or
       <TT
CLASS="LITERAL"
>advisory</TT
>
      </TD
></TR
><TR
><TD
><TT
CLASS="STRUCTFIELD"
>database</TT
></TD
><TD
><TT
CLASS="TYPE"
>oid</TT
></TD
><TD
><TT
CLASS="LITERAL"
><A
HREF="catalog-pg-database.html"
><TT
CLASS="STRUCTNAME"
>pg_database</TT
></A
>.oid</TT
></TD
><TD
>       OID of the database in which the object exists, or
       zero if the object is a shared object, or
       null if the object is a transaction ID
      </TD
></TR
><TR
><TD
><TT
CLASS="STRUCTFIELD"
>relation</TT
></TD
><TD
><TT
CLASS="TYPE"
>oid</TT
></TD
><TD
><TT
CLASS="LITERAL"
><A
HREF="catalog-pg-class.html"
><TT
CLASS="STRUCTNAME"
>pg_class</TT
></A
>.oid</TT
></TD
><TD
>       OID of the relation, or null if the object is not
       a relation or part of a relation
      </TD
></TR
><TR
><TD
><TT
CLASS="STRUCTFIELD"
>page</TT
></TD
><TD
><TT
CLASS="TYPE"
>integer</TT
></TD
><TD
>&nbsp;</TD
><TD
>       Page number within the relation, or null if the object
       is not a tuple or relation page
      </TD
></TR
><TR
><TD
><TT
CLASS="STRUCTFIELD"
>tuple</TT
></TD
><TD
><TT
CLASS="TYPE"
>smallint</TT
></TD
><TD
>&nbsp;</TD
><TD
>       Tuple number within the page, or null if the object is not a tuple
      </TD
></TR
><TR
><TD
><TT
CLASS="STRUCTFIELD"
>virtualxid</TT
></TD
><TD
><TT
CLASS="TYPE"
>text</TT
></TD
><TD
>&nbsp;</TD
><TD
>       Virtual ID of a transaction, or null if the object is not a
       virtual transaction ID
      </TD
></TR
><TR
><TD
><TT
CLASS="STRUCTFIELD"
>transactionid</TT
></TD
><TD
><TT
CLASS="TYPE"
>xid</TT
></TD
><TD
>&nbsp;</TD
><TD
>       ID of a transaction, or null if the object is not a transaction ID
      </TD
></TR
><TR
><TD
><TT
CLASS="STRUCTFIELD"
>classid</TT
></TD
><TD
><TT
CLASS="TYPE"
>oid</TT
></TD
><TD
><TT
CLASS="LITERAL"
><A
HREF="catalog-pg-class.html"
><TT
CLASS="STRUCTNAME"
>pg_class</TT
></A
>.oid</TT
></TD
><TD
>       OID of the system catalog containing the object, or null if the
       object is not a general database object
      </TD
></TR
><TR
><TD
><TT
CLASS="STRUCTFIELD"
>objid</TT
></TD
><TD
><TT
CLASS="TYPE"
>oid</TT
></TD
><TD
>any OID column</TD
><TD
>       OID of the object within its system catalog, or null if the
       object is not a general database object
      </TD
></TR
><TR
><TD
><TT
CLASS="STRUCTFIELD"
>objsubid</TT
></TD
><TD
><TT
CLASS="TYPE"
>smallint</TT
></TD
><TD
>&nbsp;</TD
><TD
>       Column number targeted by the lock (the
       <TT
CLASS="STRUCTFIELD"
>classid</TT
> and <TT
CLASS="STRUCTFIELD"
>objid</TT
> refer to the
       table itself),
       or zero if the target is some other general database object,
       or null if the target is not a general database object
      </TD
></TR
><TR
><TD
><TT
CLASS="STRUCTFIELD"
>virtualtransaction</TT
></TD
><TD
><TT
CLASS="TYPE"
>text</TT
></TD
><TD
>&nbsp;</TD
><TD
>       Virtual ID of the transaction that is holding or awaiting this lock
      </TD
></TR
><TR
><TD
><TT
CLASS="STRUCTFIELD"
>pid</TT
></TD
><TD
><TT
CLASS="TYPE"
>integer</TT
></TD
><TD
>&nbsp;</TD
><TD
>       Process ID of the server process holding or awaiting this
       lock, or null if the lock is held by a prepared transaction
      </TD
></TR
><TR
><TD
><TT
CLASS="STRUCTFIELD"
>mode</TT
></TD
><TD
><TT
CLASS="TYPE"
>text</TT
></TD
><TD
>&nbsp;</TD
><TD
>Name of the lock mode held or desired by this process (see <A
HREF="explicit-locking.html#LOCKING-TABLES"
>Section 13.3.1</A
> and <A
HREF="transaction-iso.html#XACT-SERIALIZABLE"
>Section 13.2.3</A
>)</TD
></TR
><TR
><TD
><TT
CLASS="STRUCTFIELD"
>granted</TT
></TD
><TD
><TT
CLASS="TYPE"
>boolean</TT
></TD
><TD
>&nbsp;</TD
><TD
>True if lock is held, false if lock is awaited</TD
></TR
></TBODY
></TABLE
></DIV
><P
>   <TT
CLASS="STRUCTFIELD"
>granted</TT
> is true in a row representing a lock
   held by the indicated transaction.  False indicates that this transaction is
   currently waiting to acquire this lock, which implies that some other
   transaction is holding a conflicting lock mode on the same lockable object.
   The waiting transaction will sleep until the other lock is released (or a
   deadlock situation is detected). A single transaction can be waiting to
   acquire at most one lock at a time.
  </P
><P
>   Every transaction holds an exclusive lock on its virtual transaction ID for
   its entire duration.  If a permanent ID is assigned to the transaction
   (which normally happens only if the transaction changes the state of the
   database), it also holds an exclusive lock on its permanent transaction ID
   until it ends.  When one transaction finds it necessary to wait specifically
   for another transaction, it does so by attempting to acquire share lock on
   the other transaction ID (either virtual or permanent ID depending on the
   situation). That will succeed only when the other transaction
   terminates and releases its locks.
  </P
><P
>   Although tuples are a lockable type of object,
   information about row-level locks is stored on disk, not in memory,
   and therefore row-level locks normally do not appear in this view.
   If a transaction is waiting for a
   row-level lock, it will usually appear in the view as waiting for the
   permanent transaction ID of the current holder of that row lock.
  </P
><P
>   Advisory locks can be acquired on keys consisting of either a single
   <TT
CLASS="TYPE"
>bigint</TT
> value or two integer values.
   A <TT
CLASS="TYPE"
>bigint</TT
> key is displayed with its
   high-order half in the <TT
CLASS="STRUCTFIELD"
>classid</TT
> column, its low-order half
   in the <TT
CLASS="STRUCTFIELD"
>objid</TT
> column, and <TT
CLASS="STRUCTFIELD"
>objsubid</TT
> equal
   to 1.  Integer keys are displayed with the first key in the
   <TT
CLASS="STRUCTFIELD"
>classid</TT
> column, the second key in the <TT
CLASS="STRUCTFIELD"
>objid</TT
>
   column, and <TT
CLASS="STRUCTFIELD"
>objsubid</TT
> equal to 2.  The actual meaning of
   the keys is up to the user.  Advisory locks are local to each database,
   so the <TT
CLASS="STRUCTFIELD"
>database</TT
> column is meaningful for an advisory lock.
  </P
><P
>   <TT
CLASS="STRUCTNAME"
>pg_locks</TT
> provides a global view of all locks
   in the database cluster, not only those relevant to the current database.
   Although its <TT
CLASS="STRUCTFIELD"
>relation</TT
> column can be joined
   against <TT
CLASS="STRUCTNAME"
>pg_class</TT
>.<TT
CLASS="STRUCTFIELD"
>oid</TT
> to identify locked
   relations, this will only work correctly for relations in the current
   database (those for which the <TT
CLASS="STRUCTFIELD"
>database</TT
> column
   is either the current database's OID or zero).
  </P
><P
>   The <TT
CLASS="STRUCTFIELD"
>pid</TT
> column can be joined to the
   <TT
CLASS="STRUCTFIELD"
>procpid</TT
> column of the
   <TT
CLASS="STRUCTNAME"
>pg_stat_activity</TT
> view to get more
   information on the session holding or waiting to hold each lock.
   Also, if you are using prepared transactions, the
   <TT
CLASS="STRUCTFIELD"
>transaction</TT
> column can be joined to the
   <TT
CLASS="STRUCTFIELD"
>transaction</TT
> column of the
   <TT
CLASS="STRUCTNAME"
>pg_prepared_xacts</TT
> view to get more
   information on prepared transactions that hold locks.
   (A prepared transaction can never be waiting for a lock,
   but it continues to hold the locks it acquired while running.)
  </P
><P
>   The <TT
CLASS="STRUCTNAME"
>pg_locks</TT
> view displays data from both the
   regular lock manager and the predicate lock manager, which are
   separate systems.  When this view is accessed, the internal data
   structures of each lock manager are momentarily locked, and copies are
   made for the view to display.  Each lock manager will therefore
   produce a consistent set of results, but as we do not lock both lock
   managers simultaneously, it is possible for locks to be taken or
   released after we interrogate the regular lock manager and before we
   interrogate the predicate lock manager.  Each lock manager is only
   locked for the minimum possible time so as to reduce the performance
   impact of querying this view, but there could nevertheless be some
   impact on database performance if it is frequently accessed.
  </P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="view-pg-indexes.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="view-pg-prepared-statements.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><TT
CLASS="STRUCTNAME"
>pg_indexes</TT
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="catalogs.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><TT
CLASS="STRUCTNAME"
>pg_prepared_statements</TT
></TD
></TR
></TABLE
></DIV
></BODY
></HTML
>